﻿using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Xml.XPath;
using DPLAB.IPAMS.DataEntities;
using DPLAB.IPAMS.DataLoader.Interface;

namespace DPLAB.IPAMS.DataLoader.Excel
{
    /// <summary>
    /// Loads and process Excel file 
    /// </summary>
    public class ExcelLoader : IDataLoader
    {
        private readonly IMapper mapper;
        private readonly string sourceFilePath;

        /// <summary>
        /// Initializes a new instance of the <see cref="ExcelLoader"/> class.
        /// </summary>
        /// <param name="sourceFilePath">The source file path.</param>
        /// <param name="mapper">The mapper.</param>
        /// <remarks>
        /// The mapper which the constructor uses maps tabs of the excel to tables in the data set and columns from the tabs into table columns.
        /// </remarks>
        public ExcelLoader(string sourceFilePath, IMapper mapper)
        {
            this.sourceFilePath = sourceFilePath;
            this.mapper = mapper;
        }

        public ExcelLoader(string sourceFilePath)
            : this(sourceFilePath, CreateDefaultMapper())
        {
        }

        #region IDataLoader Members

        public IPAMSDataSet Load()
        {
            //Demo for IPAMS groups
            IPAMSDataSet ipamsDataSet;
            using (DataSet dataSet = GetWorkbookAsDataSet())
            {
                ipamsDataSet = new IPAMSDataSet();

                foreach (DataTable targetTable in ipamsDataSet.Tables)
                {
                    if (!dataSet.Tables.Contains(mapper.FindOriginalTableName(targetTable)))
                        throw new StrongTypingException(
                            string.Format("Missing table {0}",
                                          mapper.FindOriginalTableName(targetTable)));
                    foreach (DataColumn targetColumn in targetTable.Columns)
                        if (
                            !dataSet.Tables[mapper.FindOriginalTableName(targetTable)].Columns.Contains(
                                 mapper.FindOriginalColumnName(targetColumn)))
                            throw new StrongTypingException(
                                string.Format("In table {0} missing column {1}",
                                              mapper.FindOriginalTableName(targetTable),
                                              mapper.FindOriginalColumnName(targetColumn)));
                }

                TranslateDateSet(dataSet);

                string[] tablesInOrder;
                using (
                    var reader =
                        new StreamReader(
                            GetType().Assembly.GetManifestResourceStream("DPLAB.IPAMS.DataLoader.Excel.TablesOrder.txt"))
                    )
                {
                    string tablesNames = reader.ReadToEnd();
                    tablesInOrder = tablesNames.Split(new[] {Environment.NewLine}, StringSplitOptions.RemoveEmptyEntries);
                }

                foreach (string tableName in tablesInOrder)
                    foreach (DataRow row in dataSet.Tables[tableName].Rows)
                    {
                        if (NotEmpty(row))
                            try
                            {
                                DataRow newRow = ipamsDataSet.Tables[tableName].NewRow();
                                foreach (DataColumn column in ipamsDataSet.Tables[tableName].Columns)
                                    newRow[column.ColumnName] = row[column.ColumnName];
                                ipamsDataSet.Tables[tableName].Rows.Add(newRow);
                            }
                            catch (ConstraintException e)
                            {
                                e.Data.Add("Table", mapper.FindOriginalTableName(dataSet.Tables[tableName]));
                                e.Data.Add("Row", dataSet.Tables[tableName].Rows.IndexOf(row) + 2);
                                throw;
                            }
                            catch (InvalidConstraintException e)
                            {
                                e.Data.Add("Table",
                                           mapper.FindOriginalTableName(dataSet.Tables[tableName]));
                                e.Data.Add("Row", dataSet.Tables[tableName].Rows.IndexOf(row) + 2);
                                throw;
                            }
                            catch (ArgumentException e)
                            {
                                e.Data.Add("Table",
                                           mapper.FindOriginalTableName(dataSet.Tables[tableName]));
                                e.Data.Add("Row", dataSet.Tables[tableName].Rows.IndexOf(row) + 2);
                                throw;
                            }
                            catch (NoNullAllowedException e)
                            {
                                e.Data.Add("Table",
                                           mapper.FindOriginalTableName(dataSet.Tables[tableName]));
                                e.Data.Add("Row", dataSet.Tables[tableName].Rows.IndexOf(row) + 2);
                                throw;
                            }
                    }
            }
            return ipamsDataSet;
        }

        #endregion

        private static IMapper CreateDefaultMapper()
        {
            Stream stream =
                typeof (ExcelLoader).Assembly.GetManifestResourceStream(
                    "DPLAB.IPAMS.DataLoader.Excel.ExcelToDSMapping.xml");
            return new DataMapper(new XPathDocument(stream));
        }

        private static bool NotEmpty(DataRow row)
        {
            foreach (object o in row.ItemArray)
                if (o != null && !string.IsNullOrEmpty(o.ToString().Trim()))
                    return true;
            return false;
        }

        private void TranslateDateSet(DataSet dataSet)
        {
            foreach (DataTable table in dataSet.Tables)
            {
                foreach (DataColumn column in table.Columns)
                    column.ColumnName = mapper.MapColumnName(column);
                table.TableName = mapper.MapTableName(table);
            }
        }

        private DataSet GetWorkbookAsDataSet()
        {
            DataSet excelDataSet;
            using (
                var excelConnection =
                    new OleDbConnection(
                        string.Format(
                            "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES\"",
                            sourceFilePath)))
            {
                using (var excelCommand = new OleDbCommand())
                {
                    excelCommand.Connection = excelConnection;
                    using (var excelAdapter = new OleDbDataAdapter(excelCommand))
                    {
                        excelConnection.Open();
                        using (
                            DataTable excelSheets = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
                                                                                        new object[]
                                                                                            {null, null, null, "TABLE"})
                            )
                        {
                            excelDataSet = new DataSet();

                            foreach (DataRow tabNameRow in excelSheets.Rows)
                            {
                                var tabName = (string) tabNameRow["TABLE_NAME"];
                                if (tabName.EndsWith("_"))
                                    continue;
                                DataTable tabTable =
                                    excelDataSet.Tables.Add(tabName.Remove(tabName.Length - 1, 1));
                                excelCommand.CommandText = string.Format(@"SELECT * FROM [{0}]", tabName);
                                try
                                {
                                    excelAdapter.Fill(tabTable);
                                }
                                catch (ArgumentException e)
                                {
                                    e.Data.Add("Table", tabTable.TableName);
                                    throw;
                                }
                            }
                        }
                    }
                }

                excelConnection.Close();
            }
            return excelDataSet;
        }
    }
}